"""
    连接数据库的步骤
    1. 导入需要的包
        1. mysql-connector
        2. mysql-connector-python
    2. 配置连接

"""
import sys

import mysql.connector

from com.hanamaki.study.db.util import JDBCUtils

"""
    编写数据库中的  CRUD Transaction   SQL注入
"""


def create_table():
    cursor = None
    con = None
    try:
        con = JDBCUtils.get_con()
    except mysql.connector.Error:
        print('数据连接错误')
        sys.exit()
    else:
        print('连接成功')

        table_sql = 'create table if not exists `python_test_table`(' \
                    'id int not null comment \'id号\',' \
                    'name varchar(255) null comment \'姓名\'' \
                    ');'

        cursor = con.cursor()
        cursor.execute(table_sql)

    finally:
        cursor.close()
        con.close()


def insert_data(id, name):
    cursor = None
    con = None
    try:
        con = JDBCUtils.get_con()
    except mysql.connector.Error:
        print('数据连接错误')
        sys.exit()
    else:
        print('连接成功')
        insert_sql = "insert into `python_test_table`(`id`,`name`) values(" + str(id) + ",'" + name + "');"
        print(insert_sql)
        cursor = con.cursor()
        cursor.execute(insert_sql)
        # mysql  是支持 事务    提交
        con.commit()
    finally:
        cursor.close()
        con.close()


def insert_data_prepared(data):
    cursor = None
    con = None
    try:
        con = JDBCUtils.get_con()
    except mysql.connector.Error:
        print('数据连接错误')
        sys.exit()
    else:
        print('连接成功')
        insert_sql = "insert into `python_test_table`(`id`,`name`) values(%s,%s)"
        print(insert_sql)
        cursor = con.cursor()
        cursor.executemany(insert_sql, data)
        # mysql  是支持 事务    提交
        con.commit()
    finally:
        cursor.close()
        con.close()


def insert_data(id, name):
    cursor = None
    con = None
    try:
        con = JDBCUtils.get_con()
    except mysql.connector.Error:
        print('数据连接错误')
        sys.exit()
    else:
        print('连接成功')
        insert_sql = "insert into `python_test_table`(`id`,`name`) values(" + str(id) + ",'" + name + "');"
        print(insert_sql)
        cursor = con.cursor()
        cursor.execute(insert_sql)
        # mysql  是支持 事务    提交
        con.commit()
    finally:
        cursor.close()
        con.close()


def query_ById(id):
    cursor = None
    con = None
    try:
        con = JDBCUtils.get_con()
    except mysql.connector.Error:
        print('数据连接错误')
        sys.exit()
    else:
        print('连接成功')
        # query_sql = "select * from python_test_table where id = " + str(id)
        print(id)
        query_sql = "select * from python_test_table where id =%s"
        print(query_sql)
        cursor = con.cursor()
        # cursor.execute(query_sql)
        cursor.execute(query_sql, id)
        print("查询id为", str(id), "的记录为:", cursor.fetchall())

    finally:
        cursor.close()
        con.close()


def delete_ById(id):
    cursor = None
    con = None
    try:
        con = JDBCUtils.get_con()
    except mysql.connector.Error:
        print('数据连接错误')
        sys.exit()
    else:
        print('连接成功')
        query_sql = "delete from python_test_table where id =%s"
        print(query_sql)
        cursor = con.cursor()
        cursor.execute(query_sql, id)
        con.commit()
    finally:
        cursor.close()
        con.close()


def update_ById(data):
    cursor = None
    con = None
    try:
        con = JDBCUtils.get_con()
    except mysql.connector.Error:
        print('数据连接错误')
        sys.exit()
    else:
        print('连接成功')
        query_sql = "update python_test_table set `name` = %s where id = %s"
        print(query_sql)
        cursor = con.cursor()
        cursor.execute(query_sql, data)
        con.commit()
    finally:
        cursor.close()
        con.close()


# 查询,模拟sql 注入
def query_login(data):
    cursor = None
    con = None
    try:
        con = JDBCUtils.get_con()
    except mysql.connector.Error:
        print('数据连接错误')
        sys.exit()
    else:
        print('连接成功')
        # query_sql = "select * from `login` where user_name = '" + username + "' and pass_word = '" + password + "'"
        query_sql = "select * from `login` where user_name = %s and pass_word = %s"
        print(query_sql)
        cursor = con.cursor()
        cursor.execute(query_sql, data)
        print(cursor.fetchall())

    finally:
        cursor.close()
        con.close()


# 操作 事务  转账的 操作
def trans_action_money(transferId, collectionId, money):
    cursor = None
    con = None
    try:
        con = JDBCUtils.get_con()
        print('连接成功')
        cursor = con.cursor()
        sql1 = "update python_test_table set money = money - %s where id = %s"
        cursor.execute(sql1, (money, transferId,))
        con.commit()
        sql2 = "update python_test_table set money = money + %s where id = %s"

        cursor.execute(sql2, (money, collectionId,))
        con.commit()
        print('事务成功')
    except:
        print('事务出错')
        con.rollback()
    finally:
        cursor.close()
        con.close()


if __name__ == '__main__':
    create_table()
    # insert_data(1, 'hanamaki')

    # list_data = [
    #     (3, "jojo"),
    #     (4, "jack")
    # ]
    # insert_data_prepared(list_data)

    # query_ById((1,))
    # delete_ById((3,))
    # update_ById(('jojo', 2,))
    # query_login('hanamaki', '123456')
    # query_login(" ' or '1=1 ", " ' or '1=1")
    # query_login(('hanamaki', '123456',))
    # query_login((" ' or '1=1 ", " ' or '1=1",))

    trans_action_money(1, 2, 500)
